﻿#region Using directives

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Data.SqlServerCe;

#endregion

namespace Microsoft.Samples.SqlServer
{
    /// <summary>
    /// Summary description for form.
    /// </summary>
    public partial class MainForm : System.Windows.Forms.Form
    {
        private const string localDatabaseName = @"\My Documents\AWMobile.sdf";
        private const string publisherDatabase = @"AdventureWorks";
        private const string publicationName = @"AdvWorksSalesOrders";
        private const string subscriberName = @"AWMobileSubscriber";
        private const string dataSource = @"Data Source=" + localDatabaseName;

        Boolean subscriptionExists;
       
        private SqlCeReplication replication;
        private SqlCeConnection conn;
        private SqlCeResultSet scrsOrders;
        private SqlCeResultSet scrsCategories;
        private SqlCeResultSet scrsProducts;
        private SqlCeResultSet scrsOrderItems;


        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1823:AvoidUnusedPrivateFields")]
        private int salesPersonID;
        private int customerID;
        private int salesOrderID;
        private int salesOrderDetailID;
        private int productCategoryID;
        private int productID;
        private int orderQuantity;
        private string tableName;
        private int percentage;

        private enum ProductDisplay
        {
            Inventory,
            NewItem,
            ExistingItem
        };
        private ProductDisplay productDisplay = ProductDisplay.ExistingItem;

        private EventHandler userInterfaceUpdateEvent;
        private SyncStatus eventStatus;

        /// <summary>
        /// Synchronization settings.
        /// </summary>
        enum SyncStatus
        {
            PercentComplete,
            BeginUpload,
            BeginDownload,
            SyncComplete,
            Error
        }

        /// <summary>
        /// Create the main form.
        /// </summary>
        public MainForm()
        {
            InitializeComponent();

            this.userInterfaceUpdateEvent
                = new EventHandler(UserInterfaceUpdateEvent);
        }

        /// <summary>
        /// Load the main form and initialize data.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void MainForm_Load(object sender, EventArgs e)
        {
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // Set the subscriber connection string. If the database had more than one subscription, 
                // we would also specify Publisher, Publication, and PublisherDatabase properties.
                this.replication = new SqlCeReplication();
                this.replication.SubscriberConnectionString = dataSource;

                // Check if database exists and create if not
                if (File.Exists(localDatabaseName) == true)
                {
                    // Create new connection and leave open for the duration of the application
                    this.conn = new SqlCeConnection(dataSource);
                    this.conn.Open();

                    // Load the replication settings
                    LoadSubscriptionSettings();

                    if (CheckEmployeeTableExists() == true)
                    {
                        // Retrieve the salesperson ID
                        RetrieveSalesPerson();

                        // Retrieve and display the customers and orders
                        ViewCustomerOrders();
                    }
                    else
                    {
                        // Display the synchronization settings
                        ShowPanel(settingsPanel);
                    }
                }
                else
                {
                    // Create new database
                    SqlCeEngine engine
                        = new SqlCeEngine(dataSource);
                    engine.CreateDatabase();
                    engine.Dispose();

                    // Create new connection and leave open for the 
                    // duration of the application
                    this.conn = new SqlCeConnection(dataSource);
                    this.conn.Open();

                    // Load the default settings.
                    LoadDefaultValues();

                    // Display the synchronization settings
                    ShowPanel(settingsPanel);
                }

                // Select the first synchronization method
                this.synchronizeMethodComboBox.SelectedIndex = 0;

                ClearStatusTextBox();
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                Cursor.Current = cursor;
            }
        }

        private void MainForm_Closing(object sender, System.ComponentModel.CancelEventArgs e)
        {
            if (this.replication != null)
            {
                this.replication.Dispose();
                this.replication = null;
            }

            if (this.conn != null)
            {
                this.conn.Close();
                this.conn.Dispose();
                this.conn = null;
            }
        }

        /// <summary>
        /// Handle the file view orders menu selection.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void fileViewOrdersMenuItem_Click(object sender, EventArgs e)
        {
            ViewCustomerOrders();
        }

        /// <summary>
        /// Handle the file view inventory menu selection.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void fileViewInventoryMenuItem_Click(object sender, EventArgs e)
        {
            this.productDisplay = ProductDisplay.Inventory;
            this.productID = 0;

            LoadSalesOrderProduct();
        }

        /// <summary>
        /// Handle the file exit menu selection.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void fileExitMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                this.Close();
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        /// <summary>
        /// Handle the tools synchronize menu selection.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolsSynchronizeMenuItem_Click(object sender, EventArgs e)
        {
            // Display the synchronize panel
            UpdateButtons(true);
            ShowPanel(synchronizePanel);
        }

        /// <summary>
        /// Handle the tools settings menu selection.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolsSettingsMenuItem_Click(object sender, EventArgs e)
        {
            // Display the replication settings panel
            ShowPanel(settingsPanel);
        }

        /// <summary>
        /// Check to see if the Employee table exists in the database.
        /// </summary>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private bool CheckEmployeeTableExists()
        {
            bool result = false;
            SqlCeCommand cmd = null;
            int count;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // Load the employees
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT COUNT(*) "
                    + @"FROM [INFORMATION_SCHEMA].[TABLES] "
                    + @"WHERE [TABLE_NAME] = 'Employee'";
                count = (int)cmd.ExecuteScalar();
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }

            return result;
        }

        /// <summary>
        /// Retrieve this sales person ID.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void RetrieveSalesPerson()
        {
            SqlCeCommand cmd = null;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // Load the employees
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT [EmployeeID] "
                    + @"FROM [Employee]";

                this.salesPersonID = (int)cmd.ExecuteScalar();
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        /// <summary>
        /// Show the designated panel and hide all the other panels.
        /// </summary>
        /// <param name="panel"></param>
        private void ShowPanel(Panel panel)
        {
            foreach (Control control in this.Controls)
            {
                if (control is Panel)
                {
                    if (control == panel)
                    {
                        control.Visible = true;
                    }
                    else
                    {
                        control.Visible = false;
                    }
                }
            }
        }

        /// <summary>
        /// Load the product and display panel for editing.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void orderItemsDataGrid_DoubleClick(object sender, EventArgs e)
        {
            int rowIndex;

            // If user clicks on the product name column then open the 
            // editing form for that ProductID
            if (this.orderItemsDataGrid.CurrentCell.ColumnNumber == 0)
            {
                this.productDisplay = ProductDisplay.ExistingItem;
                rowIndex = this.orderItemsDataGrid.CurrentRowIndex;
                if (rowIndex != -1)
                {
                    this.scrsOrderItems.ReadAbsolute(rowIndex);
                }

                this.salesOrderDetailID
                    = (int)this.scrsOrderItems[0]; //@"SalesOrderDetailID"
                this.productID = (int)this.scrsOrderItems[2]; //@"ProductID"

                LoadSalesOrderProduct();
            }
        }
  
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1300:SpecifyMessageBoxOptions")]
        private static void ReportError(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

        #region Products

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void productsCategoryComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                if (this.productsCategoryComboBox.SelectedValue is int)
                {
                    this.productCategoryID = (int)this.productsCategoryComboBox.SelectedValue;

                    // Load the sales order items and prices
                    cmd = this.conn.CreateCommand();
                    cmd.CommandText
                        = @"SELECT p.[ProductID], p.[Name], p.[ListPrice] "
                        + @"FROM [Product] p "
                        + @"INNER JOIN [ProductSubcategory] ps "
                        + @"ON p.[ProductSubcategoryID] = ps.[ProductSubcategoryID] "
                        + @"WHERE ps.[ProductCategoryID] = @ProductCategoryID "
                        + @"ORDER BY p.[Name]";

                    parm = new SqlCeParameter(@"@ProductCategoryID", SqlDbType.Int);
                    parm.Value = this.productCategoryID;
                    cmd.Parameters.Add(parm);

                    this.scrsProducts
                        = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

                    this.productsProductComboBox.DisplayMember = @"Name";
                    this.productsProductComboBox.ValueMember = @"ProductID";
                    this.productsProductComboBox.DataSource = this.scrsProducts;
                    this.productsProductComboBox.SelectedIndex = 0;
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void productsProductComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            SqlCeResultSet scrsProductInfo;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                if (this.productsProductComboBox.SelectedValue is int)
                {
                    if (this.productDisplay != ProductDisplay.ExistingItem)
                    {
                        this.productID = (int)this.productsProductComboBox.SelectedValue;
                    }

                    // Load the sales order items and prices
                    cmd = this.conn.CreateCommand();
                    cmd.CommandText
                        = @"SELECT p.[ListPrice], SUM(pi.[Quantity]) AS [Quantity] "
                        + @"FROM [Product] p "
                        + @"INNER JOIN [ProductInventory] pi "
                        + @"ON p.[ProductID] = pi.[ProductID] "
                        + @"WHERE p.[ProductID] = @ProductID "
                        + @"GROUP BY p.[ProductID], p.[ListPrice]; ";

                    parm = new SqlCeParameter(@"@ProductID", SqlDbType.Int);
                    parm.Value = this.productID;
                    cmd.Parameters.Add(parm);

                    scrsProductInfo
                        = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                    if (scrsProductInfo.HasRows == true)
                    {
                        scrsProductInfo.ReadFirst();
                        switch (this.productDisplay)
                        {
                            case ProductDisplay.Inventory:
                                // Get quantity from inventory
                                this.orderQuantity = (int)(long)scrsProductInfo[1]; //@"Quantity "
                                break;

                            case ProductDisplay.ExistingItem:
                                // Get quantity from the sales order
                                if (this.scrsOrderItems != null)
                                {
                                    this.orderQuantity = (int)this.scrsOrderItems[1]; //@"OrderQty "
                                }

                                break;

                            case ProductDisplay.NewItem:
                                this.orderQuantity = 0;
                                break;
                        }

                        this.productsQuantityNumericUpDown.Value = this.orderQuantity;
                        this.productsUnitPriceTextBox.Text
                             = scrsProductInfo[0].ToString(); //@"ListPrice "
                        this.productsTotalCostTextBox.Text
                            = (this.orderQuantity
                            * (decimal)scrsProductInfo[0]).ToString(
                            System.Globalization.CultureInfo.InvariantCulture); //@"ListPrice "

                        scrsProductInfo.Dispose();
                    }
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        /// <summary>
        /// Save the changes to the product selection to the database.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void productsSaveButton_Click(object sender, EventArgs e)
        {
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // Insert the sales order details
                cmd = this.conn.CreateCommand();
                switch (this.productDisplay)
                {
                    case ProductDisplay.NewItem:
                        cmd.CommandText
                        = @"INSERT INTO [SalesOrderDetail] "
                        + @"([SalesOrderID], [OrderQty], [ProductID], [UnitPrice], [SpecialOfferID]) "
                        + @"VALUES "
                        + @"(@SalesOrderID, @OrderQty, @ProductID, @UnitPrice, 1)";

                        parm = new SqlCeParameter(@"@SalesOrderID", SqlDbType.Int);
                        parm.Value = this.salesOrderID;
                        cmd.Parameters.Add(parm);

                        parm = new SqlCeParameter(@"@OrderQty", SqlDbType.Int);
                        parm.Value = Convert.ToInt32(this.productsQuantityNumericUpDown.Text, System.Globalization.CultureInfo.InvariantCulture);
                        cmd.Parameters.Add(parm);

                        parm = new SqlCeParameter(@"@ProductID", SqlDbType.Int);
                        parm.Value = this.productID;
                        cmd.Parameters.Add(parm);

                        parm = new SqlCeParameter(@"@UnitPrice", SqlDbType.Money);
                        parm.Value = Convert.ToDecimal(this.productsUnitPriceTextBox.Text, System.Globalization.CultureInfo.InvariantCulture);
                        cmd.Parameters.Add(parm);

                        break;

                    case ProductDisplay.ExistingItem:
                        cmd.CommandText
                            = @"UPDATE [SalesOrderDetail] "
                        + @"SET [OrderQty] = @OrderQty "
                        + @"WHERE [SalesOrderID] = @SalesOrderID "
                        + @"AND [SalesOrderDetailID] = @SalesOrderDetailID "
                        + @"AND [ProductID] = @ProductID ";

                        parm = new SqlCeParameter(@"@OrderQty", SqlDbType.Int);
                        parm.Value = Convert.ToInt32(this.productsQuantityNumericUpDown.Text, System.Globalization.CultureInfo.InvariantCulture);
                        cmd.Parameters.Add(parm);

                        parm = new SqlCeParameter(@"@SalesOrderID", SqlDbType.Int);
                        parm.Value = this.salesOrderID;
                        cmd.Parameters.Add(parm);

                        parm = new SqlCeParameter(@"@SalesOrderDetailID", SqlDbType.Int);
                        parm.Value = this.salesOrderDetailID;
                        cmd.Parameters.Add(parm);

                        parm = new SqlCeParameter(@"@ProductID", SqlDbType.Int);
                        parm.Value = this.productID;
                        cmd.Parameters.Add(parm);

                        break;
                }

                cmd.ExecuteNonQuery();
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }

            // Reload the order items to show the addition
            LoadSalesOrderItems();

            // Display the order items edit panel after saving data changes
            ShowPanel(orderItemsPanel);
        }

        /// <summary>
        /// Cancel the changes to the sales order product selection.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void productsCancelButton_Click(object sender, EventArgs e)
        {
            // Display the order items edit panel without saving data changes
            ShowPanel(orderItemsPanel);
        }

        /// <summary>
        /// Load the product information for the selected sales order item.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void LoadSalesOrderProduct()
        {
            SqlCeCommand cmd = null;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                if (this.productsCategoryComboBox.DataSource == null)
                {
                    // Product categories
                    cmd = this.conn.CreateCommand();
                    cmd.CommandText
                        = @"SELECT pc.[ProductCategoryID], pc.[Name] "
                        + @"FROM [ProductCategory] pc "
                        + @"ORDER BY pc.[Name]";

                    this.scrsCategories
                        = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

                    // Reset the data bindings
                    this.productsCategoryComboBox.DataSource = this.scrsCategories;
                    this.productsCategoryComboBox.DisplayMember = @"Name";
                    this.productsCategoryComboBox.ValueMember = @"ProductCategoryID";
                    this.productsCategoryComboBox.SelectedIndex = 0;
                }

                // Displaying product information
                switch (this.productDisplay)
                {
                    case ProductDisplay.Inventory:
                        this.productsCategoryComboBox.Enabled = true;
                        this.productsProductComboBox.Enabled = true;
                        this.productsQuantityNumericUpDown.Enabled = false;

                        this.productsSaveButton.Enabled = false;
                        this.productsCancelButton.Enabled = false;

                        break;

                    case ProductDisplay.ExistingItem:
                        this.productsCategoryComboBox.Enabled = false;
                        this.productsProductComboBox.Enabled = false;
                        this.productsQuantityNumericUpDown.Enabled = true;

                        UpdateCurrentProductCategoryID();
                        this.productsCategoryComboBox.SelectedValue = this.productCategoryID;
                        this.productsProductComboBox.SelectedValue = this.productID;

                        this.productsSaveButton.Enabled = true;
                        this.productsCancelButton.Enabled = true;

                        break;

                    case ProductDisplay.NewItem:
                        this.productsCategoryComboBox.SelectedIndex = -1;
                        this.productsProductComboBox.SelectedIndex = -1;
                        this.productsQuantityNumericUpDown.Value = 1;

                        this.productsCategoryComboBox.Enabled = true;
                        this.productsProductComboBox.Enabled = true;
                        this.productsQuantityNumericUpDown.Enabled = true;

                        this.productsSaveButton.Enabled = true;
                        this.productsCancelButton.Enabled = true;

                        break;
                }

                // Display the products panel
                ShowPanel(productsPanel);
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        /// <summary>
        /// Change the product category to match the selected product.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void UpdateCurrentProductCategoryID()
        {
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // Load the sales order items and prices
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT pc.[ProductCategoryID] "
                    + @"FROM [Product] p "
                    + @"INNER JOIN [ProductSubcategory] ps "
                    + @"ON p.[ProductSubcategoryID] = ps.[ProductSubcategoryID] "
                    + @"INNER JOIN [ProductCategory] pc "
                    + @"ON ps.[ProductCategoryID] = pc.[ProductCategoryID] "
                    + @"WHERE p.[ProductID] = @ProductID ";

                parm = new SqlCeParameter(@"@ProductID", SqlDbType.Int);
                parm.Value = this.productID;
                cmd.Parameters.Add(parm);

                this.productCategoryID = (int)cmd.ExecuteScalar();
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        #endregion

        #region SalesOrders

        /// <summary>
        /// Load the sales orders for the selected customer.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ordersCustomersComboBox_SelectedIndexChanged(
            object sender, EventArgs e)
        {
            LoadCustomerOrders();
        }

        /// <summary>
        /// Delete the selected order from the database.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void ordersDeleteOrderButton_Click(object sender, EventArgs e)
        {
            SqlCeTransaction transaction;
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            // Create transaction
            transaction = this.conn.BeginTransaction();

            this.salesOrderID = (int)this.ordersDataGrid[this.ordersDataGrid.CurrentRowIndex, 0];

            try
            {
                if (this.salesOrderID > 0)
                {
                    // Delete the selected order detail records
                    cmd = this.conn.CreateCommand();
                    cmd.Transaction = transaction;
                    cmd.CommandText
                        = @"DELETE FROM [SalesOrderDetail] "
                        + @"WHERE [SalesOrderID] = @SalesOrderID";

                    parm = new SqlCeParameter(@"@SalesOrderID", SqlDbType.Int);
                    parm.Value = this.salesOrderID;
                    cmd.Parameters.Add(parm);

                    cmd.ExecuteNonQuery();

                    // Delete the selected order
                    cmd = this.conn.CreateCommand();
                    cmd.Transaction = transaction;
                    cmd.CommandText
                        = @"DELETE FROM [SalesOrderHeader] "
                        + @"WHERE [SalesOrderID] = @SalesOrderID";

                    parm = new SqlCeParameter(@"@SalesOrderID", SqlDbType.Int);
                    parm.Value = this.salesOrderID;
                    cmd.Parameters.Add(parm);

                    cmd.ExecuteNonQuery();

                    // Commit the transaction if we get here
                    transaction.Commit(CommitMode.Immediate);

                    LoadCustomerOrders();
                }
            }
            catch (SqlCeException ex)
            {
                transaction.Rollback();

                ReportError(ex);
            }
            catch (Exception ex)
            {
                transaction.Rollback();

                ReportError(ex);
            }
            finally
            {
                if (transaction != null)
                {
                    transaction.Dispose();
                }

                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        /// <summary>
        /// Load the sales order for the currently selected customer.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void LoadCustomerOrders()
        {
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                if (this.ordersCustomersComboBox.SelectedValue is int)
                {
                    this.customerID
                        = (int)this.ordersCustomersComboBox.SelectedValue;

                    // Load the sales orders
                    cmd = this.conn.CreateCommand();
                    cmd.CommandText
                        = @"SELECT [SalesOrderID], "
                        + @"CASE [Status] "
                        + @"WHEN 1 THEN 'In process' "
                        + @"WHEN 2 THEN 'Approved' "
                        + @"WHEN 3 THEN 'Backordered' "
                        + @"WHEN 4 THEN 'Rejected' "
                        + @"WHEN 5 THEN 'Shipped' "
                        + @"WHEN 6 THEN 'Cancelled' "
                        + @"ELSE '*** Invalid ***' "
                        + @"END AS [StatusText] "
                        + @"FROM [SalesOrderHeader] "
                        + @"WHERE [CustomerID] = @CustomerID "
                        + @"ORDER BY [SalesOrderID]";

                    parm = new SqlCeParameter(@"@CustomerID", SqlDbType.Int);
                    parm.Value = this.customerID;
                    cmd.Parameters.Add(parm);

                    SqlCeResultSet oldResults = this.scrsOrders;

                    this.scrsOrders
                        = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

                    if (oldResults != null)
                    {
                        oldResults.Dispose();
                    }

                    this.ordersDataGrid.DataSource
                        = this.scrsOrders.ResultSetView;

                    if (this.scrsOrders.HasRows)
                    {
                        // Enable delete button
                        ordersDeleteOrderButton.Enabled = true;
                    }
                    else
                    {
                        // Disable delete button
                        ordersDeleteOrderButton.Enabled = false;
                    }
                }
                else
                {
                    this.ordersDataGrid.DataSource = null;

                    // Disable delete button
                    ordersDeleteOrderButton.Enabled = false;
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        /// <summary>
        /// Retrieve and display the customers and orders for this salesperson.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void ViewCustomerOrders()
        {
            SqlCeResultSet scrsCustomers = null;
            SqlCeCommand cmd = null;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // Load the customers
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT [CustomerID], [Name] "
                    + @"FROM [Store] "
                    + @"ORDER BY [Name]";

                scrsCustomers
                    = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

                this.ordersCustomersComboBox.DataSource
                    = scrsCustomers.ResultSetView;
                this.ordersCustomersComboBox.DisplayMember = @"Name";
                this.ordersCustomersComboBox.ValueMember = @"CustomerID";

                scrsCustomers.Dispose();

                // Display the customers and orders
                ShowPanel(ordersPanel);
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        #endregion

        #region Synchronize

        /// <summary>
        /// Clear the status field to initial values.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void synchronizeClearStatusButton_Click(object sender, EventArgs e)
        {
            ClearStatusTextBox();
        }

        /// <summary>
        /// Stop the synchronization process.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void synchronizeStopButton_Click(object sender, EventArgs e)
        {
            try
            {
                this.replication.CancelSynchronize();

                UpdateStatus(Properties.Resources.SynchronizationCanceled);
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        /// <summary>
        /// Synchronize the database with the publisher.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1300:SpecifyMessageBoxOptions"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void synchronizeSynchronizeButton_Click(object sender, EventArgs e)
        {
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // If the subscription is not initialized, then we can't set properties.
                if (subscriptionExists)
                {
                    if (this.replication.InternetPassword == null)
                    {
                        // The replication object will not return stored passwords. 
                        // Warn the user to re-supply the password.
                        MessageBox.Show(string.Format(System.Globalization.CultureInfo.InvariantCulture,
                            Properties.Resources.PasswordMessage, this.replication.InternetLogin));

                        // Cancel the synchronization and show the settings panel.
                        UpdateButtons(true);
                        ShowPanel(this.settingsPanel);
                        return;

                    }
                    
                    // Update the hostname property.
                    this.replication.HostName = this.synchronizeHostNameTextBox.Text;

                    // Set the exchange type.
                    switch (this.synchronizeMethodComboBox.SelectedIndex)
                    {
                        case 0:
                            this.replication.ExchangeType = ExchangeType.BiDirectional;
                            break;
                        case 1:
                            this.replication.ExchangeType = ExchangeType.Upload;
                            break;
                        case 2:
                            this.replication.ReinitializeSubscription(false);
                            break;
                        case 3:
                            this.replication.ReinitializeSubscription(true);
                            break;
                        default:
                            this.replication.ExchangeType = ExchangeType.BiDirectional;
                            break;
                    }
                    //this.replication.SaveProperties();
                }
                // Set buttons and clear status
                UpdateButtons(false);

                ClearStatusTextBox();

                // Synchronize to the SQL Server database
                this.replication.BeginSynchronize(
                    new AsyncCallback(this.SyncCompletedCallback),
                    new OnStartTableUpload(this.OnStartTableUploadCallback),
                    new OnStartTableDownload(this.OnStartTableDownloadCallback),
                    new OnSynchronization(this.OnSynchronizationCallback),
                    this.replication);
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                // Reset the cursor
                Cursor.Current = cursor;
            }
        }

        /// <summary>
        /// Get the last date and time a succesful synchronization occurred.
        /// </summary>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public DateTime LastSuccessfulSyncTime()
        {
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            DateTime utcDateTime;
            DateTime localDateTime = DateTime.Now;

            try
            {
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT [LastSuccessfulSync] "
                    + @"FROM [__sysMergeSubscriptions] "
                    + @"WHERE [Publication] = @Publication";

                parm = new SqlCeParameter(@"@Publication",
                    SqlDbType.NVarChar, 256);
                parm.Value = publicationName;
                cmd.Parameters.Add(parm);

                utcDateTime = (DateTime)cmd.ExecuteScalar();
                localDateTime = utcDateTime.ToLocalTime();
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);

            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }

            return localDateTime;
        }

        /// <summary>
        /// Add progress text to the status text box based on how complete we are.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1300:SpecifyMessageBoxOptions"), 
        System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void UserInterfaceUpdateEvent(object sender, System.EventArgs e)
        {
            try
            {
                switch (this.eventStatus)
                {
                    case SyncStatus.BeginUpload:
                        UpdateStatus(Properties.Resources.UploadingTable + this.tableName);
                        break;

                    case SyncStatus.PercentComplete:
                        UpdateStatus(string.Format(System.Globalization.CultureInfo.InvariantCulture,
                            Properties.Resources.PercentComplete, this.percentage));
                        break;

                    case SyncStatus.BeginDownload:
                        UpdateStatus(Properties.Resources.DownloadingTable + this.tableName);
                        break;

                    case SyncStatus.SyncComplete:
                        UpdateStatus(Properties.Resources.SynchronizationCompletedSuccessfully);

                        UpdateStatus(LastSuccessfulSyncTime().ToString());

                        if (synchronizeNotifyCheckBox.CheckState
                            == CheckState.Checked)
                        {
                            MessageBox.Show(Properties.Resources.SynchronizationCompleted);
                        }

                        subscriptionExists = true;

                        // Enable the Save Settings button when synchronization completes.
                        this.settingsSaveButton.Enabled = true;
                        this.settingsResetButton.Enabled = true;

                        // Reset the buttons
                        UpdateButtons(true);

                        break;

                    case SyncStatus.Error:

                        // Enable the Save Settings button when synchronization completes.
                        this.settingsSaveButton.Enabled = true;
                        this.settingsResetButton.Enabled = true;

                        UpdateButtons(true);
                        break;
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        /// <summary>
        /// Synchronization completed callback function on separate thread.
        /// </summary>
        /// <param name="ar"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public void SyncCompletedCallback(IAsyncResult ar)
        {
            SqlCeReplication repl = null;

            if (ar == null)
            {
                return;
            }

            try
            {
                repl = (SqlCeReplication)ar.AsyncState;

                // Stop the synchronization. This method returns an exception
                // if an error occured during synchronization.
                repl.EndSynchronize(ar);

                // Save the replication properties.
                repl.SaveProperties();

                this.eventStatus = SyncStatus.SyncComplete;
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);

                this.eventStatus = SyncStatus.Error;
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                // NOTE: If you'd like to set Control properties from within this 
                // method, you need to use Control.Invoke method in order to marshal
                // the call to the UI thread; otherwise you might deadlock your 
                // application; See Control.Invoke documentation for more information

                this.Invoke(this.userInterfaceUpdateEvent);

            }
        }

        /// <summary>
        /// Start table upload callback function on separate thread.
        /// </summary>
        /// <param name="ar"></param>
        /// <param name="tableName"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public void OnStartTableUploadCallback(IAsyncResult ar, string uploadTableName)
        {
            try
            {
                this.tableName = uploadTableName;
                this.eventStatus = SyncStatus.BeginUpload;

                // NOTE: If you'd like to set Control properties from within this 
                // method, you need to use Control.Invoke method in order to marshal
                // the call to the UI thread; otherwise you might deadlock your 
                // application; See Control.Invoke documentation for more information

                this.Invoke(this.userInterfaceUpdateEvent);
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        /// <summary>
        /// Start table download callback function on separate thread.
        /// </summary>
        /// <param name="ar"></param>
        /// <param name="tableName"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public void OnStartTableDownloadCallback(IAsyncResult ar, string downloadTableName)
        {
            try
            {
                this.tableName = downloadTableName;
                this.eventStatus = SyncStatus.BeginDownload;

                // NOTE: If you'd like to set Control properties from within this 
                // method, you need to use Control.Invoke method in order to marshal
                // the call to the UI thread; otherwise you might deadlock your 
                // application; See Control.Invoke documentation for more information

                this.Invoke(this.userInterfaceUpdateEvent);
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        /// <summary>
        /// Synchronization callback function on separate thread.
        /// </summary>
        /// <param name="ar"></param>
        /// <param name="percentComplete"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public void OnSynchronizationCallback(IAsyncResult ar, int percentComplete)
        {
            try
            {
                this.percentage = percentComplete;
                this.eventStatus = SyncStatus.PercentComplete;

                // NOTE: If you'd like to set Control properties from within this 
                // method, you need to use Control.Invoke method in order to marshal
                // the call to the UI thread; otherwise you might deadlock your 
                // application; See Control.Invoke documentation for more information

                this.Invoke(this.userInterfaceUpdateEvent);
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        /// <summary>
        /// Add the text to the status text box and scroll to the bottom of the text.
        /// </summary>
        /// <param name="text"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void UpdateStatus(string statusText)
        {
            try
            {
                // Append the new text
                this.synchronizeStatusTextBox.Text += statusText + "\r\n";

                // Scroll to the bottom of the text.
                this.synchronizeStatusTextBox.SelectionStart
                    = this.synchronizeStatusTextBox.Text.Length;
                this.synchronizeStatusTextBox.ScrollToCaret();
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        /// <summary>
        /// Clear the text from the status text box.
        /// </summary>
        private void ClearStatusTextBox()
        {
            this.synchronizeStatusTextBox.Text
                = Properties.Resources.InitialStatus;
        }

        private void UpdateButtons(bool enabled)
        {
            this.synchronizeClearStatusButton.Enabled = enabled;
            this.synchronizeSynchronizeButton.Enabled = enabled;
            this.synchronizeStopButton.Enabled = !enabled;
        }

        #endregion

        #region UserSettings

        /// <summary>
        /// Save the connection settings to the database.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void settingsSaveButton_Click(object sender, EventArgs e)
        {
            SaveSubscriptionSettings();

            // Display the synchronize panel
            UpdateButtons(true);
            ShowPanel(synchronizePanel);
        }

        /// <summary>
        /// Clear the fields to initial values.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void settingsResetButton_Click(object sender, EventArgs e)
        {
            LoadDefaultValues();
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1300:SpecifyMessageBoxOptions")]
        private void settingsUseAnonymous_CheckStateChanged(object sender, EventArgs e)
        {
            // Set the authentication mode use to connect to the Web server.
            if (settingsUseAnonymousCheckBox.Checked)
            {
                this.settingsLoginTextBox.Enabled = false;
                this.settingsPasswordTextBox.Enabled = false;
                this.settingsLoginTextBox.Text = string.Empty;
                this.settingsPasswordTextBox.Text = string.Empty;
                this.settingsLoginLabel.Enabled = false;
                this.settingsPasswordLabel.Enabled = false;
            }
            else
            {
                if (settingsInternetUrlTextBox.Text.Split(new Char[] { ':' })[0] != "https")
                {
                    // If supplying Windows credentials and SSL is not used,
                    // display a security message box.
                    MessageBox.Show(Properties.Resources.SslMessage, 
                        Properties.Resources.SecurityAlert);
                }
                this.settingsLoginTextBox.Text = Properties.Resources.Login;
                this.settingsPasswordTextBox.Text = string.Empty;
                this.settingsLoginTextBox.Enabled = true;
                this.settingsPasswordTextBox.Enabled = true;
                this.settingsLoginLabel.Enabled = true;
                this.settingsPasswordLabel.Enabled = true;
            }
        }

        /// <summary>
        /// Loads replication settings if the subscription exists.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void LoadSubscriptionSettings()
        {
            SqlCeCommand cmd = null;
            int count;

            try
            {
                // Check for the replication settings
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT COUNT(*) "
                    + @"FROM [INFORMATION_SCHEMA].[TABLES] "
                    + @"WHERE [TABLE_NAME] = '__sysMergeSubscriptions'";
                count = (int)cmd.ExecuteScalar();
                if (count > 0)
                {
                    // Load properties for the existing subscription.
                    this.replication.LoadProperties();

                    subscriptionExists = true;

                    // Load subscription properties into the user settings text boxes.
                    this.settingsSqlServerTextBox.Text = this.replication.Publisher;
                    this.settingsInternetUrlTextBox.Text = this.replication.InternetUrl;
                    this.synchronizeHostNameTextBox.Text = this.replication.HostName;
                    if (this.replication.InternetLogin == null)
                    {
                        // If a login value is not set, assume that we are using anonymous access.
                        this.settingsUseAnonymousCheckBox.Checked = true;
                    }
                    else
                    {
                        this.settingsLoginTextBox.Text = this.replication.InternetLogin;
                    }
                }
                else
                {
                    // Load default user settings.
                    LoadDefaultValues();
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
        }

        /// <summary>
        /// Load the default user settings.
        /// </summary>
        private void LoadDefaultValues()
        {
            // Load the default user settings values into the text boxes.
            this.settingsSqlServerTextBox.Text = Properties.Resources.Publisher;
            this.settingsInternetUrlTextBox.Text = Properties.Resources.InitialUrl;
            this.synchronizeHostNameTextBox.Text = Properties.Resources.Hostname;
            this.settingsUseAnonymousCheckBox.Checked = true;
            this.settingsPasswordTextBox.Text = string.Empty;
        }

        /// <summary>
        /// Save the current subscription settings.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void SaveSubscriptionSettings()
        {
            try
            {
                // Set subscription properties.
                this.replication.InternetUrl = this.settingsInternetUrlTextBox.Text;
                this.replication.InternetLogin = this.settingsLoginTextBox.Text;
                this.replication.InternetPassword = this.settingsPasswordTextBox.Text;
                this.replication.Publisher = this.settingsSqlServerTextBox.Text;
                this.replication.Distributor = this.settingsSqlServerTextBox.Text;
                this.replication.PublisherDatabase = publisherDatabase;
                this.replication.PublisherSecurityMode
                    = SecurityType.NTAuthentication;
                this.replication.DistributorSecurityMode
                    = SecurityType.NTAuthentication;
                this.replication.Publication = publicationName;
                this.replication.Subscriber = subscriberName;
                this.replication.HostName = this.synchronizeHostNameTextBox.Text;

                if (!subscriptionExists)
                {
                    // Create a new subscription.
                    this.replication.AddSubscription(AddOption.ExistingDatabase);

                    // Disable the Save Settings button until synchronization succeeds.
                    this.settingsSaveButton.Enabled = false;
                    this.settingsResetButton.Enabled = false;
                }
                else
                {
                    // Save subscription properties.
                    this.replication.SaveProperties();
                }
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
        }

        #endregion

        #region SalesOrderItems

        /// <summary>
        /// Open the product edit panel.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void orderItemsAddItemButton_Click(object sender, EventArgs e)
        {
            this.productDisplay = ProductDisplay.NewItem;
            this.productID = 0;

            LoadSalesOrderProduct();
        }

        /// <summary>
        /// Delete the selected product from the sales order in the database.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void orderItemsDeleteItemButton_Click(object sender, EventArgs e)
        {
            int rowIndex;
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            rowIndex = this.orderItemsDataGrid.CurrentRowIndex;
            this.scrsOrderItems.ReadAbsolute(rowIndex);
            this.salesOrderDetailID
                = (int)this.scrsOrderItems[0]; //@"SalesOrderDetailID"
            this.productID = (int)this.scrsOrderItems[2]; //@"ProductID"

            try
            {
                if ((this.salesOrderID > 0)
                    && (this.salesOrderDetailID > 0)
                    && (this.productID > 0))
                {
                    // Delete the selected order detail records
                    cmd = this.conn.CreateCommand();
                    cmd.CommandText
                        = @"DELETE FROM [SalesOrderDetail] "
                        + @"WHERE [SalesOrderID] = @SalesOrderID "
                        + @"AND [SalesOrderDetailID] = @SalesOrderDetailID "
                        + @"AND [ProductID] = @ProductID";

                    parm = new SqlCeParameter(@"@SalesOrderID", SqlDbType.Int);
                    parm.Value = this.salesOrderID;
                    cmd.Parameters.Add(parm);

                    parm = new SqlCeParameter(@"@SalesOrderDetailID", SqlDbType.Int);
                    parm.Value = this.salesOrderDetailID;
                    cmd.Parameters.Add(parm);

                    parm = new SqlCeParameter(@"@ProductID", SqlDbType.Int);
                    parm.Value = this.productID;
                    cmd.Parameters.Add(parm);

                    cmd.ExecuteNonQuery();

                    LoadSalesOrderItems();
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        /// <summary>
        /// Load the sales order items and display panel for editing.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ordersDataGrid_DoubleClick(object sender, EventArgs e)
        {
            // If user clicks on the Sales Order ID column then open the 
            // editing form for that SalesOrderID
            if (this.ordersDataGrid.VisibleRowCount > 0)
            {
                this.salesOrderID = (int)this.ordersDataGrid[this.ordersDataGrid.CurrentRowIndex, 0];

                LoadSalesOrderItems();

                // Display the order items edit panel
                ShowPanel(orderItemsPanel);
            }
        }

        /// <summary>
        /// Load the sales order items for the currently selected sales order.
        /// </summary>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void LoadSalesOrderItems()
        {
            SqlCeCommand cmd = null;
            SqlCeParameter parm;
            SqlCeResultSet scrsOrderTotal;
            Cursor cursor;

            cursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            try
            {
                // Load the sales order items and prices
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT soh.[SalesOrderID], soh.[SubTotal] "
                    + @"FROM [SalesOrderHeader] soh "
                    + @"WHERE soh.[SalesOrderID] = @SalesOrderID";

                parm = new SqlCeParameter(@"@SalesOrderID", SqlDbType.Int);
                parm.Value = this.salesOrderID;
                cmd.Parameters.Add(parm);

                scrsOrderTotal
                    = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

                // Reset the data bindings
                this.orderItemsOrderNumberTextBox.DataBindings.Clear();
                this.orderItemsOrderNumberTextBox.DataBindings.Add(
                    @"Text", scrsOrderTotal, @"SalesOrderID");

                this.orderItemsTotalTextBox.DataBindings.Clear();
                this.orderItemsTotalTextBox.DataBindings.Add(
                    @"Text", scrsOrderTotal, @"SubTotal");

                scrsOrderTotal.Dispose();

                // Load the sales order items and prices
                cmd = this.conn.CreateCommand();
                cmd.CommandText
                    = @"SELECT sod.[SalesOrderDetailID], sod.[OrderQty], "
                    + @"sod.[ProductID], p.[Name], sod.[UnitPrice] "
                    + @"FROM [SalesOrderDetail] sod "
                    + @"INNER JOIN [Product] p "
                    + @"ON sod.[ProductID] = p.[ProductID] "
                    + @"WHERE sod.[SalesOrderID] = @SalesOrderID "
                    + @"ORDER BY sod.[SalesOrderDetailID]";

                parm = new SqlCeParameter(@"@SalesOrderID", SqlDbType.Int);
                parm.Value = this.salesOrderID;
                cmd.Parameters.Add(parm);

                SqlCeResultSet oldResults = this.scrsOrderItems;

                this.scrsOrderItems
                    = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

                if (oldResults != null)
                {
                    oldResults.Dispose();
                }

                this.orderItemsDataGrid.DataSource
                    = this.scrsOrderItems.ResultSetView;

                if (this.scrsOrderItems.HasRows)
                {
                    // Enable delete button
                    orderItemsDeleteItemButton.Enabled = true;
                }
                else
                {
                    // Disable delete button
                    orderItemsDeleteItemButton.Enabled = false;
                }
            }
            catch (SqlCeException ex)
            {
                ReportError(ex);
            }
            catch (Exception ex)
            {
                ReportError(ex);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }

                Cursor.Current = cursor;
            }
        }

        #endregion

    }
}
